package com.galen.manual.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.galen.manual.entity.SmsCode;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDateTime;

/**
 * 短信验证码Mapper接口
 */
@Mapper
public interface SmsCodeMapper extends BaseMapper<SmsCode> {

    /**
     * 查询有效的验证码
     * @param phone 手机号
     * @param businessType 业务类型
     * @return 验证码信息
     */
    @Select("SELECT * FROM sms_codes WHERE phone = #{phone} AND business_type = #{businessType} " +
            "AND used = 0 AND expire_time > #{now} AND deleted = 0 " +
            "ORDER BY create_time DESC LIMIT 1")
    SmsCode selectValidCode(String phone, String businessType, LocalDateTime now);

    /**
     * 标记验证码为已使用
     * @param id 验证码ID
     * @param usedTime 使用时间
     * @return 更新结果
     */
    @Update("UPDATE sms_codes SET used = 1, used_time = #{usedTime} WHERE id = #{id}")
    int markAsUsed(Long id, LocalDateTime usedTime);

    /**
     * 清理过期的验证码
     * @param expireTime 过期时间
     * @return 清理数量
     */
    @Update("UPDATE sms_codes SET deleted = 1 WHERE expire_time < #{expireTime} AND deleted = 0")
    int cleanExpiredCodes(LocalDateTime expireTime);

    /**
     * 查询指定手机号指定业务类型未使用的验证码数量
     * @param phone 手机号
     * @param businessType 业务类型
     * @param now 当前时间
     * @return 验证码数量
     */
    @Select("SELECT COUNT(*) FROM sms_codes WHERE phone = #{phone} AND business_type = #{businessType} " +
            "AND used = 0 AND expire_time > #{now} AND deleted = 0")
    int countValidCodes(String phone, String businessType, LocalDateTime now);
}